#!/usr/bin/env python
# encoding: utf-8
"""
#-------------------------------------------------------------------#
#                   CONFIDENTIAL --- CUSTOM STUDIOS                 #
#-------------------------------------------------------------------#
#                                                                   #
#                   @Project Name : 李建                #
#                                                                   #
#                   @File Name    : clearjgsql.py                   #
#                                                                   #
#                   @Programmer   : Adam                            #
#                                                                   #
#                   @Start Date   : 2020-7-15 15:10                 #
#                                                                   #
#                   @Last Update  : 2020-7-15 16:06                 #
#                                                                   #
#-------------------------------------------------------------------#
# Classes: 用于清理加工服务器中导入但在采集服务器中被清理的sql数据     #
#                                                                   #
#-------------------------------------------------------------------#
"""
import time

import pymysql

# 这是采集服务器的mysql数据库的连接属性
CJMYSQL_CONNECT = {
    'MYSQL_HOST': '114.115.219.219',
    'MYSQL_PORT': 3306,
    'MYSQL_DB': 'globallegaldatabase',
    'MYSQL_USER': 'root',
    'MYSQL_PASSWD': 'xinanzhengfadaqxueapp03!',
    'MYSQL_CHARSET': 'utf8'
}

# 这是加工服务器的mysql数据库连接属性
BSMYSQL_CONNEXT = {
    'MYSQL_HOST': '114.116.40.73',
    'MYSQL_PORT': 3306,
    'MYSQL_DB': 'southwestpoliticsun',
    'MYSQL_USER': 'lj',
    'MYSQL_PASSWD': 'xinanzhengfadaqxuelj!',
    'MYSQL_CHARSET': 'utf8'
}

# 连接采集数据库
CJMYSQL_TABLE = ['lawtext', 'lawcasetext']  # 这是采集数据库存储数据的表
cjurlconn = pymysql.connect(host=CJMYSQL_CONNECT['MYSQL_HOST'], port=CJMYSQL_CONNECT['MYSQL_PORT'],
                            db=CJMYSQL_CONNECT['MYSQL_DB'], user=CJMYSQL_CONNECT['MYSQL_USER'],
                            passwd=CJMYSQL_CONNECT['MYSQL_PASSWD'], charset='utf8')
# 连接加工服务器数据库
BSMYSQL_TABLE = {'lawtext': 'law', 'lawcasetext': '`case`'}  # 这是采集数据库存储数据的表
jgurlconn = pymysql.connect(host=BSMYSQL_CONNEXT['MYSQL_HOST'], port=BSMYSQL_CONNEXT['MYSQL_PORT'],
                            db=BSMYSQL_CONNEXT['MYSQL_DB'], user=BSMYSQL_CONNEXT['MYSQL_USER'],
                            passwd=BSMYSQL_CONNEXT['MYSQL_PASSWD'], charset='utf8')
# SQL_COUNTRYS = ['LAWCOUNTRYJPZ']
SQL_COUNTRYS = ['LAWCOUNTRYXJP', 'LAWCOUNTRYFLB', 'LAWCOUNTRYWL', 'LAWCOUNTRYMLY', 'LAWCOUNTRYTG',
                'LAWCOUNTRYYDNXY', 'LAWCOUNTRYYN', 'LAWCOUNTRYLW', 'LAWCOUNTRYMD', 'LAWCOUNTRYJPZ']
for table in CJMYSQL_TABLE:
    for country in SQL_COUNTRYS:
        i = 0
        jgtable = BSMYSQL_TABLE[table]
        cjsysid = []    # 该国在采集服务器上的Sql数据的sysid
        # 创建采集服务器游标
        cjurlcursor = cjurlconn.cursor()
        cjsql = "SELECT sysid FROM %s WHERE SortA = '%s'" % (table, country)
        try:
            cjurlcursor.execute(cjsql)
            cjresult = cjurlcursor.fetchall()
            for result in cjresult:
                cjsysid.append(result[0])
        except Exception as e:
            print('采集服务器查询错误:' + str(e))
        # 创建加工服务器游标
        jgurlcursor = jgurlconn.cursor()
        jgsql = "SELECT id, sysid FROM %s WHERE SortA = '%s'" % (jgtable, country)
        print(jgsql)
        try:
            jgurlcursor.execute(jgsql)
            jgresult = jgurlcursor.fetchall()
            for result in jgresult:
                if result[1] not in cjsysid:
                    i = i+1
                    with open('jgclearsql.txt', 'a', encoding='utf-8') as f:
                        f.write(jgtable + ' ' + country + ' ' + str(i) + ' ' + str(result[0]) + ' ' + result[1] + '\n')
                    # jgsql = "DELETE FROM %s WHERE SortA = '%s' AND SYSID = '%s' AND (SYS_FLD_MARKSTATE = '0' OR " \
                    #         "SYS_FLD_MARKSTATE = '33')" % (jgtable, country, result[1])
                    # try:
                    #     jgurlcursor.execute(jgsql)
                    #     jgurlconn.commit()
                    # except Exception as e:
                    #     print('加工服务器删除出错:' + str(e))
        except Exception as e:
            print('加工服务器查询出错:' + str(e))
